package DBLayer;

import ModelLayer.*;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

public class DbProduct implements IFDBProduct {
	private Connection con;

	public DbProduct()
	{
		con = DbConnection.getInstance().getDBcon();
	}
	
	@Override
	public int getNextProductId() {
		return GetMax.getMaxId("Select max(id) from Product");
	}
	
	@Override
	public ArrayList<Product> getAllProducts()
	{
		String wClause = " status = 'active'";
		System.out.println("Search in Product: " + wClause);
		return miscWhere(wClause);
	}
	
	@Override
	public ArrayList<Product> getProductsByName(String name)
	{   
		String wClause = " pName LIKE '%" + name + "%'";
		System.out.println("Search in Product: " + wClause);
		return miscWhere(wClause);
	}
	
	@Override
	public Product getProductById(int id)
	{   
		String wClause = " id = '" + id + "'";
		System.out.println("Search in Product: " + wClause);
		return singleWhere(wClause);
	}
	
	@Override
	public ArrayList<Product> getArrachivedProducts() {
		String wClause = " status = 'archive'";
		System.out.println("Search in Product: " + wClause);
		return miscWhere(wClause);
	}
	
	@Override
	public ArrayList<Product> getArchivedProductsByName(String productName) {
		String wClause = " pName LIKE '%" + productName + "%' AND status = 'archive'";
		System.out.println("Search in Product: " + wClause);
		return miscWhere(wClause);
	}

	@Override
	public int insertProduct(Product product) throws Exception
	{
		//call to get the next id number
		int nextID = GetMax.getMaxId("Select max(id) from Product");
		nextID = nextID + 1;
		System.out.println("next id = " +  nextID);
		int rc = -1;
		String insertquery="INSERT INTO Product(id, pName, nettPrice, tax," +
				" minStock, stockAmount, description, status";
		String valuequery = " VALUES('"+
				nextID  + "','"  +
				product.getName() + "','"  +
				product.getNettPrice() + "','"  +
				product.getTax() + "','"  +
				product.getMinStock() + "','"  +
				product.getStockAmount() + "','"  +
				product.getDescription() + "','"  +
				product.getStatus().toLowerCase() + "'";

		String query = insertquery+") "+valuequery+")"; 

		System.out.println("insert Product : " + query);
		try
		{ // insert new product
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}//end try
		catch(SQLException ex){
			System.out.println("Product not created");
			throw new Exception ("Product is not inserted correct");
		}
		return(rc);
	}

	@Override
	public int deleteProduct(Product product)
	{
		int rc=-1;

		String query="DELETE FROM product WHERE id = '" +
				product.getId() + "'";
		System.out.println(query);
		try
		{ // delete from product
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}//end try	
		catch(Exception ex){
			System.out.println("Delete exception in product db: "+ex);
		}
		return(rc);
	}

	@Override
	public int updateProduct(Product product)
	{
		int rc=-1;

		String query="UPDATE product SET "+
				"pName ='" + product.getName() + "', " +
				"nettPrice ='" + product.getNettPrice() + "', " +
				"tax ='" + product.getTax() + "', " +
				"minStock ='"+ product.getMinStock() + "', " +
				"stockAmount ='"+ product.getStockAmount() + "', " +
				"description ='"+ product.getDescription() + "', " +
				"status ='"+ product.getStatus().toLowerCase() + "' " +
				"WHERE id = '"+ product.getId() + "'";
		
		System.out.println("Update Product: " + query);
		
		try
		{ // update product
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);

			stmt.close();
		}//end try
		catch(Exception ex){
			System.out.println("Update exception in product db: "+ex);
		}
		return(rc);
	}

	private ArrayList<Product> miscWhere(String wClause)
	{
		ResultSet results;
		ArrayList<Product> list = new ArrayList<Product>();	

		String query = buildQuery(wClause);

		try
		{ // read the products from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);


			while( results.next() ){
				Product prodObj = new Product();
				prodObj = buildProduct(results);	
				list.add(prodObj);	
			}//end while
			stmt.close();  

		}//end try	
		catch(Exception e){
			System.out.println("Query exception - select product: "+e);
			e.printStackTrace();
		}
		return list;
	}

	private Product singleWhere(String wClause)
	{
		ResultSet results;
		Product prodObj = new Product();

		String query =  buildQuery(wClause);
		System.out.println(query);
		try
		{ // read the product from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			if( results.next() ){
				prodObj = buildProduct(results);
				stmt.close();
			}
			else{ //no product was found
				prodObj = null;
			}
		}//end try	
		catch(Exception e){
			System.out.println("Query exception - select product: "+e);
		}
		return prodObj;
	}

	//method to build the query
	private String buildQuery(String wClause)
	{
		String query="SELECT id, pName, nettPrice, tax," +
				" minStock, stockAmount, description, status FROM Product";

		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;

		return query;
	}
	
	private Product buildProduct(ResultSet results)
	{   Product productObj = new Product();
		try
		{ // the columns from the table product are used
			productObj.setId(results.getInt("id"));
			productObj.setName(results.getString("pName"));
			productObj.setNettPrice(results.getDouble("nettPrice"));
			productObj.setTax(results.getInt("tax"));
			productObj.setMinStock(results.getInt("minStock"));
			productObj.setStockAmount(results.getInt("stockAmount"));
			productObj.setDescription(results.getString("description"));
			productObj.setStatus(results.getString("status"));
		}
		catch(Exception e)
		{
			System.out.println("error in building the Product object");
		}
		return productObj;
	}
}
